# This is a sample Python script.
import numpy as np
import pandas as pd
# Press Shift+F10 to execute it or replace it with your code.
# Press Double Shift to search everywhere for classes, files, tool windows, actions, and settings.


def print_hi(name):
    # Use a breakpoint in the code line below to debug your script.
    print(f'Hi, {name}')  # Press Ctrl+F8 to toggle the breakpoint.

# 4’26转换
def convert0(x):
    if isinstance(x,str):#字符串格式
        minute,second = x.split("'")#将分和秒切开
        minute = int(minute)#转化
        second = int(second)
        return minute + second/60.0
    else:
        return x

# 4‘10’‘转换
def convert1(x):
    m,s = x.strip('"').split("'")
    m,s = int(m),int(s)
    return m + s/60.0

# 其他非时间字符转float
def convert2(str):
    return float(str) # str.astype(np.float)

# Press the green button in the gutter to run the script.
if __name__ == '__main__':
    print_hi('PyCharm')
    # 1、数据加载， pd.read_excel('./18级高一体测成绩汇总.xls')  默认加载第一个工作表
    data_mal = pd.read_excel('./data/18级高一体测成绩汇总.xls', sheet_name = 0)
    print(data_mal)

    # 2、数据加载， pd.read_excel('./18级高一体测成绩汇总.xls', sheet_name=1) 指定加载第二个工作表
    data_femal = pd.read_excel('./data/18级高一体测成绩汇总.xls', sheet_name = 1)
    print(data_femal)

    # 3、评分标准加载，pd.read_excel('./体侧成绩评分表.xls',header = [0,1])，header=[0,1]表示多层列索引
    std_score = pd.read_excel('./data/体侧成绩评分表.xls',header = [0,1])
    print(std_score)

    # 4、数据类型转换
    # 男1000米跑，数据类型是str，并且是4’26这种形式，需要变成float类型的值
    data_mal['男1000米跑'] = data_mal['男1000米跑'].map(convert0)
    print(data_mal.head())

    # 评分标准中男1000米跑和女800米跑的成绩都是4‘10’‘这种形式，需要转化为float类型值
    std_score.iloc[:, -4] = std_score.iloc[:,-4].map(convert1)
    std_score.iloc[:, -2] = std_score.iloc[:, -2].map(convert1)
    print(std_score)

    # 其他所有数值类型的值，都要转换为float类型的值
    data_mal.transform({'男50米跑':convert2,'男跳远':convert2,'男体前屈':convert2,'男引体':convert2,'男肺活量':convert2,'身高':convert2,'体重':convert2,'BMI':convert2})
    print(data_mal)

    # 5、对体测成绩进行分数转换
    # 注意，跑步类项目成绩越小分数越高；其他项目成绩越小分数越小
    # 1>处理男生的跑步成绩
    for col in ['男1000米跑', '男50米跑']:
        #     获取成绩的标准
        s = std_score[col]

        def convert(x):
            for i in range(len(s)):
                if x <= s['成绩'].iloc[0]:
                    if x == 0:
                        return 0  # 没有参加这个项目
                    return 100
                elif x > s['成绩'].iloc[-1]:
                    return 0  # 跑的太慢
                elif (x > s['成绩'].iloc[i - 1]) and (x <= s['成绩'].iloc[i]):
                    return s['分数'].iloc[i]

        data_mal[col + '成绩'] = data_mal[col].map(convert)
    # 2>处理女生的跑步成绩
    for col in ['女800米跑', '女50米跑']:
        #     获取成绩的标准
        s = std_score[col]

        def convert(x):
            for i in range(len(s)):
                if x <= s['成绩'].iloc[0]:
                    if x == 0:
                        return 0  # 没有参加这个项目
                    return 100
                elif x > s['成绩'].iloc[-1]:
                    return 0  # 跑的太慢
                elif (x > s['成绩'].iloc[i - 1]) and (x <= s['成绩'].iloc[i]):
                    return s['分数'].iloc[i]


        data_femal[col + '成绩'] = data_femal[col].map(convert) # 增加列
    # 3>处理男性
    for col in ['跳远', '体前屈', '引体', '肺活量']:
        col = '男' + col
        s = std_score[col]
        def convert(x):
            for i in range(len(s)):
                if x >= s['成绩'].iloc[i]:
                    return s['分数'].iloc[i]
            return 0

        data_mal[col + '成绩'] = data_mal[col].map(convert)  # 添加列

    # 4>处理女性
    for col in ['跳远', '体前屈', '仰卧', '肺活量']:
        col = '女' + col
        s = std_score[col]

        def convert(x):
            for i in range(len(s)):
                if x >= s['成绩'].iloc[i]:
                    return s['分数'].iloc[i]
            return 0

        data_femal[col + '成绩'] = data_femal[col].map(convert)  # 添加列

    cols_male = ['班级', '性别', '男1000米跑', '男1000米跑成绩', '男50米跑', '男50米跑成绩',
            '男跳远', '男跳远成绩', '男体前屈', '男体前屈成绩', '男引体', '男引体成绩', '男肺活量', '男肺活量成绩', '身高',
            '体重', 'BMI']
    data_mal = data_mal[cols_male]

    cols_female = ['班级', '性别', '女800米跑', '女800米跑成绩', '女50米跑', '女50米跑成绩',
                 '女跳远', '女跳远成绩', '女体前屈', '女体前屈成绩', '女仰卧', '女仰卧成绩', '女肺活量', '女肺活量成绩', '身高',
                 '体重', 'BMI']
    data_femal = data_femal[cols_female]

    print(data_mal)
    print(data_femal)


